using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    public class CustomerOfferPrices
    {
        public long ID = -1;
        public long CustRelID = 0;
        public long UserID = 0;
        public string Pol = "";
        public string Pod = "";
        public string Vols = "";
        public string Routers = "";
        public string Dc20 = "";
        public string Dc40 = "";
        public string Hc40 = "";
        public string Rf20 = "";
        public string Rf40 = "";
        public string CBM = "";
        public string KG = "";

        public string Dc20_Sell = "";
        public string Dc40_Sell = "";
        public string Hc40_Sell = "";
        public string Rf20_Sell = "";
        public string Rf40_Sell = "";
        public string CBM_Sell = "";
        public string KG_Sell = "";

        public string Agent = "";

        public int CostStatus = 0;
        public string CostNumber = "";
        public string CostEtd = "";
        public string CostNote = "";
        public string CustPic = "";
        public string CarrierSale = "";
        public string Other = "";
        public long Prepaid = 0;
        public long Collect = 0;

        public long Owner = 0;
        public DateTime LastUpdate = DateTime.Now;
        public DateTime CostUpDate = DateTime.Now;
        public string OfferPrice_Note = "";
        public string PricingName = "";

        public CustomerOfferPrices()
        {
        }

        public CustomerOfferPrices(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_customerofferprices";

            if (ID <= 0) // new object, we insert new record to database
            {
                strSQL = "Insert into " + strTableName + " (" +
                            "`CustRelID`" +
                            ",`UserID`" +
                            ",`Pol`" +
                            ",`Pod`" +
                            ",`Vols`" +
                            ",`Routers`" +
                            ",`Dc20`" +
                            ",`Dc40`" +
                            ",`Hc40`" +
                            ",`Rf20`" +
                            ",`Rf40`" +
                            ",`CBM`" +
                            ",`KG`" +
                            ",`Dc20_Sell`" +
                            ",`Dc40_Sell`" +
                            ",`Hc40_Sell`" +
                            ",`Rf20_Sell`" +
                            ",`Rf40_Sell`" +
                            ",`CBM_Sell`" +
                            ",`KG_Sell`" +
                            ",`Agent`" +
                            ",`LastUpdate`" +
                            ",`CostUpdate`" +
                            ",`Owner`" +
                            ",`CostNumber`" +
                            ",`CostEtd`" +
                            ",`CostNote`" +
                            ",`CostStatus`" +
                            ",`CustPic`" +
                            ",`CarrierSale`" +
                            ",`Other`" +
                            ",`Prepaid`" +
                            ",`Collect`" +
                            ",`OfferPrice_Note`" +
                            ",`PricingName`" +
                            ") " +
                         " values(" +
                             CustRelID.ToString() +
                             "," + UserID.ToString() +
                             ",'" + DBModule.RefineString(Pol) + "'" +
                             "," + "'" + DBModule.RefineString(Pod) + "'" +
                             "," + "'" + DBModule.RefineString(Vols) + "'" +
                             "," + "'" + DBModule.RefineString(Routers) + "'" +
                             "," + "'" + DBModule.RefineString(Dc20) + "'" +
                             "," + "'" + DBModule.RefineString(Dc40) + "'" +
                             "," + "'" + DBModule.RefineString(Hc40) + "'" +
                             "," + "'" + DBModule.RefineString(Rf20) + "'" +
                             "," + "'" + DBModule.RefineString(Rf40) + "'" +
                             "," + "'" + DBModule.RefineString(CBM) + "'" +
                             "," + "'" + DBModule.RefineString(KG) + "'" +
                             "," + "'" + DBModule.RefineString(Dc20_Sell) + "'" +
                             "," + "'" + DBModule.RefineString(Dc40_Sell) + "'" +
                             "," + "'" + DBModule.RefineString(Hc40_Sell) + "'" +
                             "," + "'" + DBModule.RefineString(Rf20_Sell) + "'" +
                             "," + "'" + DBModule.RefineString(Rf40_Sell) + "'" +
                             "," + "'" + DBModule.RefineString(CBM_Sell) + "'" +
                             "," + "'" + DBModule.RefineString(KG_Sell) + "'" +
                             "," + "'" + DBModule.RefineString(Agent) + "'" +
                             "," + DBModule.RefineDatetime(LastUpdate) +
                             "," + DBModule.RefineDatetime(CostUpDate) +
                             "," + Owner.ToString() +
                             "," + "'" + DBModule.RefineString(CostNumber) + "'" +
                             "," + "'" + DBModule.RefineString(CostEtd) + "'" +
                             "," + "'" + DBModule.RefineString(CostNote) + "'" +
                             "," + CostStatus.ToString() +
                             "," + "'" + DBModule.RefineString(CustPic) + "'" +
                             "," + "'" + DBModule.RefineString(CarrierSale) + "'" +
                             "," + "'" + DBModule.RefineString(Other) + "'" +
                             "," + Prepaid.ToString() +
                             "," + Collect.ToString() +
                             "," + "'" + DBModule.RefineString(OfferPrice_Note) + "'" +
                             "," + "'" + DBModule.RefineString(PricingName) + "'" +
                             ")";
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("customerofferprice", "add", cn, trans);                
            }
            else // edit object
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                    "CustRelID=" + CustRelID.ToString() +
                    ",UserID=" + UserID.ToString() +
                            ",Pol='" + DBModule.RefineString(Pol) + "'" +
                            ",Pod='" + DBModule.RefineString(Pod) + "'" +
                            ",Vols='" + DBModule.RefineString(Vols) + "'" +
                            ",Routers='" + DBModule.RefineString(Routers) + "'" +
                            ",Dc20='" + DBModule.RefineString(Dc20) + "'" +
                            ",Dc40='" + DBModule.RefineString(Dc40) + "'" +
                            ",Hc40='" + DBModule.RefineString(Hc40) + "'" +
                            ",Rf20='" + DBModule.RefineString(Rf20) + "'" +
                            ",Rf40='" + DBModule.RefineString(Rf40) + "'" +
                            ",CBM='" + DBModule.RefineString(CBM) + "'" +
                            ",KG='" + DBModule.RefineString(KG) + "'" +
                             ",Dc20_Sell='" + DBModule.RefineString(Dc20_Sell) + "'" +
                            ",Dc40_Sell='" + DBModule.RefineString(Dc40_Sell) + "'" +
                            ",Hc40_Sell='" + DBModule.RefineString(Hc40_Sell) + "'" +
                            ",Rf20_Sell='" + DBModule.RefineString(Rf20_Sell) + "'" +
                            ",Rf40_Sell='" + DBModule.RefineString(Rf40_Sell) + "'" +
                            ",CBM_Sell='" + DBModule.RefineString(CBM_Sell) + "'" +
                            ",KG_Sell='" + DBModule.RefineString(KG_Sell) + "'" +
                            ",Agent='" + DBModule.RefineString(Agent) + "'" +
                            ",LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + "" +
                            ",CostUpdate=" + DBModule.RefineDatetime(CostUpDate) + "" +
                            ",Owner = " + Owner.ToString() +
                            ",CostStatus = " + CostStatus.ToString() +
                            ",CostNumber='" + DBModule.RefineString(CostNumber) + "'" +
                            ",CostEtd='" + DBModule.RefineString(CostEtd) + "'" +
                            ",CostNote='" + DBModule.RefineString(CostNote) + "'" +
                            ",CustPic='" + DBModule.RefineString(CustPic) + "'" +
                            ",CarrierSale='" + DBModule.RefineString(CarrierSale) + "'" +
                            ",Other='" + DBModule.RefineString(Other) + "'" +
                            ",prepaid = " + Prepaid.ToString() +
                            ",Collect = " + Collect.ToString() +
                            ",OfferPrice_Note='" + DBModule.RefineString(OfferPrice_Note) + "'" +
                            ",PricingName='" + DBModule.RefineString(PricingName) + "'" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("customerofferprice", "edit", cn, trans);                
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "DELETE FROM tbl_customerofferprices WHERE ID = " + this.ID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("customerofferprice", "delete", cn, trans);
        }
        public void Delete(long CustRelID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "DELETE FROM tbl_customerofferprices WHERE CustRelID = " + CustRelID;
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("customerofferprice", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_customerofferprices WHERE ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("CustRelID")) CustRelID = long.Parse(dr["CustRelID"].ToString());
                if (!dr.IsNull("UserID")) UserID = long.Parse(dr["UserID"].ToString());
                if (!dr.IsNull("Pol")) Pol = dr["Pol"].ToString();
                if (!dr.IsNull("Pod")) Pod = dr["Pod"].ToString();
                if (!dr.IsNull("Vols")) Vols = dr["Vols"].ToString();
                if (!dr.IsNull("Routers")) Routers = dr["Routers"].ToString();
                if (!dr.IsNull("Dc20")) Dc20 = dr["Dc20"].ToString();
                if (!dr.IsNull("Dc40")) Dc40 = dr["Dc40"].ToString();
                if (!dr.IsNull("Hc40")) Hc40 = dr["Hc40"].ToString();
                if (!dr.IsNull("Rf20")) Rf20 = dr["Rf20"].ToString();
                if (!dr.IsNull("Rf40")) Rf40 = dr["Rf40"].ToString();
                if (!dr.IsNull("CBM")) CBM = dr["CBM"].ToString();
                if (!dr.IsNull("KG")) KG = dr["KG"].ToString();
                if (!dr.IsNull("Dc20_Sell")) Dc20_Sell = dr["Dc20_Sell"].ToString();
                if (!dr.IsNull("Dc40_Sell")) Dc40_Sell = dr["Dc40_Sell"].ToString();
                if (!dr.IsNull("Hc40_Sell")) Hc40_Sell = dr["Hc40_Sell"].ToString();
                if (!dr.IsNull("Rf20_Sell")) Rf20_Sell = dr["Rf20_Sell"].ToString();
                if (!dr.IsNull("Rf40_Sell")) Rf40_Sell = dr["Rf40_Sell"].ToString();
                if (!dr.IsNull("CBM_Sell")) CBM_Sell = dr["CBM_Sell"].ToString();
                if (!dr.IsNull("KG_Sell")) KG_Sell = dr["KG_Sell"].ToString();
                if (!dr.IsNull("Agent")) Agent = dr["Agent"].ToString();
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
                if (!dr.IsNull("CostUpDate")) CostUpDate = DateTime.Parse(dr["CostUpDate"].ToString());
                if (!dr.IsNull("Owner")) Owner = long.Parse(dr["Owner"].ToString());
                if (!dr.IsNull("CostNumber")) CostNumber = dr["CostNumber"].ToString();
                if (!dr.IsNull("CostEtd")) CostEtd = dr["CostEtd"].ToString();
                if (!dr.IsNull("CostNote")) CostNote = dr["CostNote"].ToString();
                if (!dr.IsNull("Other")) Other = dr["Other"].ToString();
                if (!dr.IsNull("CostStatus")) CostStatus = int.Parse(dr["CostStatus"].ToString());
                if (!dr.IsNull("CustPic")) CustPic = dr["CustPic"].ToString();
                if (!dr.IsNull("CarrierSale")) CarrierSale = dr["CarrierSale"].ToString();
                if (!dr.IsNull("Prepaid")) Prepaid = long.Parse(dr["Prepaid"].ToString());
                if (!dr.IsNull("Collect")) Collect = long.Parse(dr["Collect"].ToString());
                if (!dr.IsNull("OfferPrice_Note")) OfferPrice_Note = dr["OfferPrice_Note"].ToString();
                if (!dr.IsNull("PricingName")) PricingName = dr["PricingName"].ToString();
            }
        }
        public void GetList(string strCondition, string CustomerIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select *,CONVERT(SUBSTRING_INDEX(CostNumber,'-',-1),UNSIGNED INTEGER) AS num from tbl_customerofferprices where 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            if ((CustomerIDs != null) && (CustomerIDs != ""))
            {
                strSQL += " And CustRelID=" + CustomerIDs;
            }
            if ((OrderBy != null) && (OrderBy != "") && (OrderBy != "CostNumber"))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            else if (OrderBy == "CostNumber")
                strSQL += " Order By Num Desc";

            // run SQL statement
            //throw new Exception(strSQL);
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public void GetList(string strCondition, int userID, string CustomerIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select tbl_customerofferprices.*,CONVERT(SUBSTRING_INDEX(tbl_customerofferprices.CostNumber,'-',-1),UNSIGNED INTEGER) AS num" +
            ",tbl_customerrelation.Name as CRMNAME,tbl_customerrelation.UserID as CrmUserID,tbl_adminusers.UserName  as UserName" +
            " from tbl_customerofferprices " +
            " left join tbl_customerrelation on tbl_customerofferprices.CustRelID = tbl_customerrelation.ID " +
            " left join tbl_adminusers on tbl_customerrelation.UserID = tbl_adminusers.ID " +
            " where 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            if ((CustomerIDs != null) && (CustomerIDs != ""))
            {
                strSQL += " And CustRelID=" + CustomerIDs;
            }
            if (userID > 0)
                strSQL += " Having tbl_customerrelation.UserID=" + userID;
            if ((OrderBy != null) && (OrderBy != "") && (OrderBy != "CostNumber"))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            else if (OrderBy == "CostNumber")
                strSQL += " Order By Num Desc";

            // run SQL statement
            //throw new Exception(strSQL);
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public void DoSearch(string strCondition, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_customerofferprices where 1=1 ";
            if (strCondition.Length > 0)
                strSQL += strCondition;
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
    }
}


